<?php
if(!defined('ACCESS')) {exit('Access denied.');}
class Machine extends Base {
	// 表名
	private static $table_name = 'machine';
	// 查询字段
	private static $columns = '*';

	public static function getTableName(){
		return parent::$table_prefix.self::$table_name;
	}
	
	//列表 
	public static function getAllMachine() {
		$db=self::__instance();
		$sql="select ".self::$columns."  from ".self::getTableName()." ";
		$list = $db->query($sql)->fetchAll();
		if ($list) {
			
			return $list;
		}
		return array ();
	}

	public static function getAllMachineByField() {
		$db=self::__instance();
		$sql="select machine_id, customer_name, contacts_user , contacts_2 ,tel , host_number ,custom_code from ".self::getTableName()." ";
		$list = $db->query($sql)->fetchAll();
		if ($list) {
				
			return $list;
		}
		return array ();
	}
	

	public static function getGroupBySerialNumber($serial_number) {
		if ( $serial_number == "" ) {
			return false;
		}
		$db=self::__instance();
		$condition['serial_number'] = $serial_number;
		$list = $db->select ( self::getTableName(), self::$columns, $condition );
		if ($list) {
			return $list [0];
		}
		return array ();
	}
	
	public static  function getDbField(){
		$db=self::__instance();
		$sql = "select COLUMN_NAME,column_comment from Information_schema.columns where table_name='".self::getTableName()."'";
		$list = $db->query ($sql)->fetchAll();;
		if ($list) {
			return $list;
		}
		return array ();
	}
	
	public static function addMachine($machine_data) {
		if (! $machine_data || ! is_array ( $machine_data )) {
			return false;
		}

		$db=self::__instance();
		$id = $db->insert ( self::getTableName(), $machine_data );
		return $id;
	}

	public static function getMachineById($machine_id) {
		if (! $machine_id || ! is_numeric ( $machine_id )) {
			return false;
		}
		$db=self::__instance();
		$condition['machine_id'] = $machine_id;
		$list = $db->select ( self::getTableName(), self::$columns, $condition );
		if ($list) {
			return $list [0];
		}
		return array ();
	}
	
	public static function delMachine($machine_id) {
		if (! $machine_id || ! is_numeric ( $machine_id )) {
			return false;
		}
		$db=self::__instance();
		$condition = array("machine_id" => $machine_id);
		$result = $db->delete ( self::getTableName(), $condition );
		return $result;
	}
	
	public static function updateMachineInfo($machine_id,$machine_data) {
		if (! $machine_data || ! is_array ( $machine_data )) {
			return false;
		}
		$db=self::__instance();
		$condition=array("machine_id"=>$machine_id);
		$id = $db->update ( self::getTableName(), $machine_data,$condition );

		return $id;
	}
	
	public static function count($search_val='') {
		$db=self::__instance();

		$area_code_num = $_SESSION["osa_user_info"]["responsible_addr_code"];
		$area_code_num_arr = explode(",", $area_code_num);
		foreach ($area_code_num_arr as $item){
			$area_code_str .= "'$item'".",";
		}
		$area_code_str = rtrim($area_code_str,",");
		$company_code_str = $_SESSION["osa_user_info"]["company_code"];
		$company_code_num_arr = explode(",", $company_code_str);
		foreach ($company_code_num_arr as $item2){
			$company_code_str_new .= "'$item2'".",";
		}
		$company_code_str_new = rtrim($company_code_str_new,",");
		
		$where .="where area_code_num in ($area_code_str) and company_code_num in ($company_code_str_new) ";
		
		if($search_val != ''){
			$where .=  " and (serial_number like '%$search_val%' or  host_number like '%$search_val%' or contacts_user like '%$search_val%' or custom_code  like '%$search_val%' )";
		}
		
		$sql = " SELECT count(*) as count_num from `osa_machine` ".$where;
		$list = $db->query ($sql)->fetchAll();
		return $list["0"]["count_num"];
	}
	
	public static function getCountByDate($start_date,$end_date,$search_val) {
		$db=self::__instance();
		$area_code_num = $_SESSION["osa_user_info"]["responsible_addr_code"];
		$area_code_num_arr = explode(",", $area_code_num);
		foreach ($area_code_num_arr as $item){
			$area_code_str .= "'$item'".",";
		}
		$area_code_str = rtrim($area_code_str,",");
		$company_code_str = $_SESSION["osa_user_info"]["company_code"];
		$company_code_num_arr = explode(",", $company_code_str);
		foreach ($company_code_num_arr as $item2){
			$company_code_str_new .= "'$item2'".",";
		}
		$company_code_str_new = rtrim($company_code_str_new,",");
		$where .="where area_code_num in ($area_code_str) and company_code_num in ($company_code_str_new) ";
		if($start_date !='' && $end_date !=''){
			$where .= " and installed_date >= $start_date  and  installed_date <= $end_date ";
		}
		
		if($search_val != ''){
			$where .=  " and (serial_number like '%$search_val%' or  host_number like '%$search_val%' or contacts_user like '%$search_val%' or custom_code  like '%$search_val%' )";
		}
		$where .= " ORDER BY machine_id desc ";
		$where .= " LIMIT $start , $page_size ";
		$sql = " SELECT * from `osa_machine` ".$where;
		$list = $db->query ($sql)->fetchAll();
		return count($num);
	}
	
	public static function getMachines($class_name,$start ,$page_size,$start_date='',$end_date='',$search_val='',$orderByFiled='' ,$descOrAsc='') {
		$db=self::__instance();
		$field =$orderByFiled == ''? "machine_id" :$orderByFiled;
		$isdesc = $descOrAsc == "" ? "desc" : $descOrAsc;
// 		$condition=array();
// 		$sub_condition = array();
// 		if($class_name != ''){
// 			$sub_condition['class_name']=$class_name;
// 		}	
// 		if($start_date !='' && $end_date !=''){
// 			$sub_condition["installed_date[<>]"] =array($start_date,$end_date);
// 		}
		
// 		if($search_val != ''){
// 			$sub_condition['serial_number'] = $search_val;
// 		}
			
		
// 		if(empty($sub_condition)){
// 			$condition = array();
// 		}else{
// 			$condition["AND"] = $sub_condition;
// 		}
// 		var_dump($condition);
// 		//新增加的按照地域进行筛选数据
// 		$area_code_num = $_SESSION["osa_user_info"]["responsible_addr_code"];
// 		$condition["AND"] = array("area_code_num",array("in", implode(',',$area_code_num))) ;
// 		$company_code_num = $_SESSION["osa_user_info"]["company_code"];
// // 		$condition["AND"] = "company_code_num  in ($company_code_num)";
// 		$condition["AND"] = array("company_code_num",array("in",implode(',',$company_code_num))  );
		
// 		$condition["ORDER"]=" machine_id ASC";
// 		$condition['LIMIT']=array($start,$page_size);
// 		$list = $db->select ( self::getTableName(), self::$columns, $condition);
		$area_code_num = $_SESSION["osa_user_info"]["responsible_addr_code"];
		$area_code_num_arr = explode(",", $area_code_num);
		foreach ($area_code_num_arr as $item){
			$area_code_str .= "'$item'".",";
		}
		$area_code_str = rtrim($area_code_str,",");
		$company_code_str = $_SESSION["osa_user_info"]["company_code"];
		$company_code_num_arr = explode(",", $company_code_str);
		foreach ($company_code_num_arr as $item2){
			$company_code_str_new .= "'$item2'".",";
		}
		$company_code_str_new = rtrim($company_code_str_new,",");
		$where .="where area_code_num in ($area_code_str) and company_code_num in ($company_code_str_new) ";
		if($start_date !='' && $end_date !=''){
			$where .= " and installed_date >= $start_date  and  installed_date <= $end_date ";
		}
		
		if($search_val != ''){
			$where .=  " and (serial_number like '%$search_val%' or  host_number like '%$search_val%' or contacts_user like '%$search_val%' or custom_code  like '%$search_val%' )";
		}
		$where .= " ORDER BY $field $isdesc ";
		$where .= " LIMIT $start , $page_size ";
		$sql = " SELECT * from `osa_machine` ".$where;
		$list = $db->query ($sql)->fetchAll();
		if ($list) {
			return $list;
		}
		return array ();
	}
	
	public static function getMachinesVisitList($start,$page_size) {
		$db=self::__instance();
	
//  		$sql = " SELECT a.machine_id ,a.serial_number ,a.host_number,a.engineer ,MAX(b.create_time) AS last_time , TIMESTAMPDIFF(MONTH,MAX(b.create_time),NOW()) AS month_diff FROM  osa_machine  a LEFT JOIN osa_visitor  b ON   a.machine_id = b.id
// GROUP BY a.machine_id HAVING month_diff>=3 ";
// 		$where = " LIMIT $start , $page_size ";
		$where .= " LIMIT $start , $page_size ";
		$sql = " SELECT a.machine_id ,a.serial_number ,a.host_number,a.engineer,a.last_vistor_time FROM  osa_machine  a ".$where;
		
		$list = $db->query ($sql)->fetchAll();
		if ($list) {
			return $list;
		}
		return array ();
	}
	
	public static function getMachinesVisitListCount() {
		$db=self::__instance();
	
		//  		$sql = " SELECT a.machine_id ,a.serial_number ,a.host_number,a.engineer ,MAX(b.create_time) AS last_time , TIMESTAMPDIFF(MONTH,MAX(b.create_time),NOW()) AS month_diff FROM  osa_machine  a LEFT JOIN osa_visitor  b ON   a.machine_id = b.id
		// GROUP BY a.machine_id HAVING month_diff>=3 ";
		// 		$where = " LIMIT $start , $page_size ";
		$sql = " SELECT count(*) as count_num FROM  osa_machine  a ";
		
		$list = $db->query ($sql)->fetchAll();
		return $list["0"]["count_num"];

	}
	
	public static function visitListcount() {
		$db=self::__instance();
	
		$sql = "SELECT count(a.machine_id) as num FROM  osa_machine a ,osa_visitor  b
WHERE  a.machine_id = b.id";
	
		$num = $db->query($sql)->fetchAll();
		return $num['0']['num'];
	}
	
// 	public static function getGroupByName($group_name) {
// 		if ( $group_name == "" ) {
// 			return false;
// 		}
// 		$db=self::__instance();
// 		$condition['group_name'] = $group_name;
// 		$list = $db->select ( self::getTableName(), self::$columns, $condition );
// 		if ($list) {
// 			return $list [0];
// 		}
// 		return array ();
// 	}
	
// 	public static function updateGroupInfo($group_id,$group_data) {
// 		if (! $group_data || ! is_array ( $group_data )) {
// 			return false;
// 		}
// 		$db=self::__instance();
// 		$condition=array("group_id"=>$group_id);
// 		$id = $db->update ( self::getTableName(), $group_data,$condition );
		
// 		return $id;
// 	}
	

	
// 	public static function getGroupForOptions() {
// 		$group_list = self::getAllGroup ();
		
// 		foreach ( $group_list as $group ) {
// 			$group_options_array [$group ['group_id']] = $group ['group_name'];
// 		}
		
// 		return $group_options_array;
// 	}
	
// 	public static function getGroupUsers($group_id) {
// 		$db=self::__instance();
// 		$sql="select ".self::$columns." ,u.user_id as user_id,u.user_name as user_name,u.real_name as real_name from ".self::getTableName()." g,".User::getTableName()." u where g.group_id = $group_id and g.group_id = u.user_group order by g.group_id,u.user_id";
// 		$list = $db->query ($sql)->fetchAll();
// 		if ($list) {
// 			return $list;
// 		}
// 		return array ();
// 	}
   	/** 
	 * 创建(导出)Excel数据表格 
	 * @param  array   $list 要导出的数组格式的数据 
	 * @param  string  $filename 导出的Excel表格数据表的文件名 
	 * @param  array   $header Excel表格的表头 
	 * @param  array   $index $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值) 
	 * 比如: $header = array('编号','姓名','性别','年龄'); 
	 *       $index = array('id','username','sex','age'); 
	 *       $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24)); 
	 * @return [array] [数组] 
	 */  
	public function createtable($list,$filename,$header=array(),$index = array()){
		header("Content-type:application/vnd.ms-excel");
		header("Content-Disposition:filename=".$filename.".xls");
		$teble_header = implode("\t",$header);
		$strexport = $teble_header."\r";
		foreach ($list as $row){
			foreach($index as $val){
				$strexport.=$row[$val]."\t";
			}
			$strexport.="\r";
		
		}
		$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);
		exit($strexport);
	}
}
